﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text.RegularExpressions;
using System.IO;

/// <summary>
/// Summary description for DatabaseConnection
/// </summary>
public class CommonManager
{
    public CommonManager()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public static Int32 GetAge(DateTime dateOfBirth)
    {
        var today = DateTime.Today;

        var a = (today.Year * 100 + today.Month) * 100 + today.Day;
        var b = (dateOfBirth.Year * 100 + dateOfBirth.Month) * 100 + dateOfBirth.Day;

        return (a - b) / 10000;
    }

    public static string DateReform(string dateTextBox_Text)
    {
        string datePaid = "";
        if (dateTextBox_Text.Contains("-"))
        {
            datePaid = dateTextBox_Text.Split('-')[2] + "-" + dateTextBox_Text.Split('-')[1] + "-" + dateTextBox_Text.Split('-')[0];
        }
        else if (dateTextBox_Text.Contains("/"))
        {
            datePaid = dateTextBox_Text.Split('/')[2] + "-" + dateTextBox_Text.Split('/')[1] + "-" + dateTextBox_Text.Split('/')[0];
        }

        return datePaid;
    }

    public static string loadFeesHistory(DataRow dr_Member,string paidUpto)
    {
        string txtAnnualSubscription_Advance_Text = "0";
        string txtAnnualSubscription_Arrear_Text = "0";
        string txtAnnualSubscription_Current_Text = "0";
        string txtCompositionFeeForLifeFellowship_Text = "0";
        string txtReEnrollmentFee_Text = "0";
        string current_year_text="2014-2015";

        string allfeesHeadAnaAmount = "";
        string age = CommonManager.GetAge(DateTime.Parse(dr_Member["DateOfBirth"].ToString())).ToString();

        string sql = @"
                        SELECT  IEB.dbo.Mem_Fees.[Amount]
                          , IEB.dbo.Mem_FeesYear.Mem_FeesYearName as [PaidFor]
                          , IEB.dbo.Mem_Fees.[PaidDate]
                            , IEB.dbo.Mem_Fees.RefferenceNo
                      FROM IEB.dbo.[Mem_Fees]
                      inner join  IEB.dbo.Mem_Member on  IEB.dbo.Mem_Member.Mem_MemberID= IEB.dbo.Mem_Fees.Mem_MemberID
                      inner join  IEB.dbo.Mem_FeesYear on  IEB.dbo.Mem_FeesYear.Mem_FeesYearID= IEB.dbo.Mem_Fees.Mem_FeesYearID
                      where   IEB.dbo.Mem_Fees.Comn_RowStatusID=1 and  IEB.dbo.Mem_Member.Mem_MemberID=" + dr_Member["Mem_MemberID"].ToString() + @"
                        order by    IEB.dbo.Mem_FeesYear.Ordering  asc;
                    SELECT [AnnaralSubscriptionFee]
                      FROM  IEB.dbo.[Mem_MemberType] where Mem_MemberTypeID=" + dr_Member["Mem_MemberTypeID"].ToString() + @";

                     SELECT top 1 [Amount]
                      FROM  IEB.dbo.[Mem_FeesForLife] where Mem_MembershipTypeID=" + dr_Member["Mem_MemberTypeID"].ToString() + @" and Age<=(" + age + @"+1)
                      order by Age Desc
                    ";
        DataSet ds = DatabaseManager.ExecSQL(sql);
        decimal duesAmount = 0;
        decimal duesAmount_Current = 0;
        bool otherthanUnknown = false;
        bool unknown = false;
        bool isLife = false;
        decimal max = 0;
        int current_year = int.Parse(current_year_text.Split('-')[0]);
        bool only_unknown = false;
        decimal reEnrollmentfee = 0;
        decimal lifeAmount = decimal.Parse(ds.Tables[2].Rows.Count==0?"0":ds.Tables[2].Rows[0][0].ToString());
        if (int.Parse(age) >= 66)
        {
            paidUpto = current_year_text;
        }
        txtCompositionFeeForLifeFellowship_Text = lifeAmount.ToString("0");
        if (ds.Tables[0].Rows.Count > 0)
        {
            decimal total = 0;
            
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                if (dr["PaidFor"].ToString() == "Unknown")
                {
                    unknown = true;
                }
                else
                {
                    otherthanUnknown = true;
                }

                if (
                    ds.Tables[0].Rows[(ds.Tables[0].Rows.Count - 1)]["PaidFor"].ToString() == "ABOVE 66"
                    ||
                    ds.Tables[0].Rows[(ds.Tables[0].Rows.Count - 1)]["PaidFor"].ToString() == "LIFE"
                    )
                {
                    isLife = true;
                }
            }

            if (dr_Member["Mem_MemberTypeID"].ToString() == "1")
            {
                max = 1600;
                reEnrollmentfee = 400;
            }
            else if (dr_Member["Mem_MemberTypeID"].ToString() == "2")
            {
                max = 950;
                reEnrollmentfee = 250;
            }

            else if (dr_Member["Mem_MemberTypeID"].ToString() == "3")
            {
                max = 500;
                reEnrollmentfee = 150;
            }

            try
            {
                if (
                    ds.Tables[0].Rows[(ds.Tables[0].Rows.Count - 1)]["PaidFor"].ToString() != "ABOVE 66"
                    &&
                    ds.Tables[0].Rows[(ds.Tables[0].Rows.Count - 1)]["PaidFor"].ToString() != "LIFE"
                    && (otherthanUnknown))
                {
                    int yearDiffirence = (current_year + 1) - int.Parse(ds.Tables[0].Rows[(ds.Tables[0].Rows.Count - 1)]["PaidFor"].ToString().Split('-')[1]);
                    int yearDiffirence_current = current_year - int.Parse(ds.Tables[0].Rows[(ds.Tables[0].Rows.Count - 1)]["PaidFor"].ToString().Split('-')[1]);
                    if (yearDiffirence > 0)
                    {

                        txtAnnualSubscription_Arrear_Text = (decimal.Parse((yearDiffirence_current).ToString()) * decimal.Parse(ds.Tables[1].Rows[0][0].ToString())).ToString("0");
                        txtAnnualSubscription_Current_Text = decimal.Parse(ds.Tables[1].Rows[0][0].ToString()).ToString("0");
                        //When the offer is invalid
                        if (yearDiffirence >= 3)
                        {
                            txtReEnrollmentFee_Text = reEnrollmentfee.ToString("0");
                        }
                    }

                    /*
                       txtAnnualSubscription_Advance_Text = "0";
                     * string txtAnnualSubscription_Arrear_Text = "0";
                        string txtAnnualSubscription_Current_Text = "0";
                        string txtCompositionFeeForLifeFellowship_Text = "0";
                        string txtReEnrollmentFee_Text = "0";
                     */
                    //for advance payment
                    if (paidUpto.Contains('-'))
                    {
                        if (current_year < int.Parse(paidUpto.Split('-')[0]))
                        {
                            txtAnnualSubscription_Advance_Text = (decimal.Parse((int.Parse(paidUpto.Split('-')[0]) - current_year).ToString()) * decimal.Parse(ds.Tables[1].Rows[0][0].ToString())).ToString("0");
                        }
                    }

                    allfeesHeadAnaAmount =
                        @"txtAnnualSubscription_Advance_Text@" + txtAnnualSubscription_Advance_Text
                        + "-txtAnnualSubscription_Arrear_Text@" + txtAnnualSubscription_Arrear_Text
                        + "-txtAnnualSubscription_Current_Text@" + txtAnnualSubscription_Current_Text
                        + "-txtCompositionFeeForLifeFellowship_Text@" + txtCompositionFeeForLifeFellowship_Text
                        + "-txtReEnrollmentFee_Text@" + txtReEnrollmentFee_Text;

                }
                else if (unknown && !otherthanUnknown)
                {
                    duesAmount = max;
                    duesAmount_Current = max;
                    only_unknown = false;

                    //when offer is invalid
                    only_unknown = true;
                    allfeesHeadAnaAmount = "Unknown";
                }
                else
                {
                    allfeesHeadAnaAmount = "Life";

                }
            }
            catch (Exception ex)
            { }
        }
        else
        {
            allfeesHeadAnaAmount = "Unknown";

        }

        

        return allfeesHeadAnaAmount;
    }

    public static string getPrictureFileName(string  path,string currentMembershipNo, string previousMembershipNo)
    { 
        string picture="";
        System.IO.FileInfo file;
        file = new FileInfo(path + "//" + currentMembershipNo.Replace("/","-")+".jpg");
        if (!file.Exists)
        { 
            file = new FileInfo(path + "//" + currentMembershipNo.Replace("/","-")+".gif");
            if (!file.Exists)
            {
                if (previousMembershipNo.Trim() != "")
                {
                    file = new FileInfo(path + "//" + previousMembershipNo.Replace("/", "-") + ".jpg");
                    if (!file.Exists)
                    {
                        file = new FileInfo(path + "//" + previousMembershipNo.Replace("/", "-") + ".gif");
                        if (!file.Exists)
                        {
                            picture = "";
                        }
                        else
                        {
                            picture = currentMembershipNo.Replace("/", "-") + ".gif";
                        }
                    }
                    else
                    {
                        picture = currentMembershipNo.Replace("/", "-") + ".jpg";
                    }
                }
            }
            else
            {
                picture = currentMembershipNo.Replace("/", "-") + ".gif"; 
            }
        }
        else
        {
            picture=currentMembershipNo.Replace("/","-")+".jpg";
        }

        return picture;
    }

    public static String DateReformation(string Date)
    {
      
        string datePaid = Date;
        if (Date.Contains("-"))
        {
            datePaid = Date.Split('-')[2] + "-" + Date.Split('-')[1] + "-" + Date.Split('-')[0];
        }
        else if (Date.Contains("/"))
        {
            datePaid = Date.Split('/')[2] + "-" + Date.Split('/')[1] + "-" + Date.Split('/')[0];
        }

        return datePaid;
    }

    public static string isInjection(string field)
    {
        if(
            field.Contains("=")
            || field.Contains("--")
            || field.Contains(";")
            || field.Contains("''")
            || field.ToUpper().Contains("DROP ")
            || field.ToUpper().Contains("DELETE ")
            || field.ToUpper().Contains("UPDATE ")
            || field.ToUpper().Contains("CREATE ")
            || field.ToUpper().Contains("SELECT ")
            || field.ToUpper().Contains("INSERT ")
            //|| field.ToUpper().Contains(" AND ")
            || field.ToUpper().Contains(" OR ")
            || field.ToUpper().Contains("1=1")
            || field.ToUpper().Trim().Contains("1 = 1")
            || field.ToUpper().Trim().Contains("'1'='1'")
            )
        //if (!Regex.IsMatch(field, @"^(?=.*[a-z])(?=.*[A-Z]).{8,10}$"))
            throw new FormatException("SQL Injection");

        return field;
    }

    public static string StripHTML(string source)
    {
        try
        {
            string result;

            // Remove HTML Development formatting
            // Replace line breaks with space
            // because browsers inserts space
            result = source.Replace("\r", " ");
            // Replace line breaks with space
            // because browsers inserts space
            result = result.Replace("\n", " ");
            // Remove step-formatting
            result = result.Replace("\t", string.Empty);
            // Remove repeating spaces because browsers ignore them
            result = System.Text.RegularExpressions.Regex.Replace(result,
                                                                  @"( )+", " ");

            // Remove the header (prepare first by clearing attributes)
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"<( )*head([^>])*>", "<head>",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"(<( )*(/)( )*head( )*>)", "</head>",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     "(<head>).*(</head>)", string.Empty,
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            // remove all scripts (prepare first by clearing attributes)
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"<( )*script([^>])*>", "<script>",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"(<( )*(/)( )*script( )*>)", "</script>",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            //result = System.Text.RegularExpressions.Regex.Replace(result,
            //         @"(<script>)([^(<script>\.</script>)])*(</script>)",
            //         string.Empty,
            //         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"(<script>).*(</script>)", string.Empty,
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            // remove all styles (prepare first by clearing attributes)
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"<( )*style([^>])*>", "<style>",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"(<( )*(/)( )*style( )*>)", "</style>",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     "(<style>).*(</style>)", string.Empty,
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            // insert tabs in spaces of <td> tags
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"<( )*td([^>])*>", "\t",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            // insert line breaks in places of <BR> and <LI> tags
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"<( )*br( )*>", "\r",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"<( )*li( )*>", "\r",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            // insert line paragraphs (double line breaks) in place
            // if <P>, <DIV> and <TR> tags
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"<( )*div([^>])*>", "\r\r",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"<( )*tr([^>])*>", "\r\r",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"<( )*p([^>])*>", "\r\r",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            // Remove remaining tags like <a>, links, images,
            // comments etc - anything that's enclosed inside < >
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"<[^>]*>", string.Empty,
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            // replace special characters:
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @" ", " ",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"•", " * ",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"‹", "<",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"›", ">",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"™", "(tm)",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"⁄", "/",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"<", "<",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @">", ">",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"©", "(c)",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"®", "(r)",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            // Remove all others. More can be added, see
            // http://hotwired.lycos.com/webmonkey/reference/special_characters/
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     @"&(.{2,6});", string.Empty,
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            // for testing
            //System.Text.RegularExpressions.Regex.Replace(result,
            //       this.txtRegex.Text,string.Empty,
            //       System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            // make line breaking consistent
            result = result.Replace("\n", "\r");

            // Remove extra line breaks and tabs:
            // replace over 2 breaks with 2 and over 4 tabs with 4.
            // Prepare first to remove any whitespaces in between
            // the escaped characters and remove redundant tabs in between line breaks
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     "(\r)( )+(\r)", "\r\r",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     "(\t)( )+(\t)", "\t\t",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     "(\t)( )+(\r)", "\t\r",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     "(\r)( )+(\t)", "\r\t",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            // Remove redundant tabs
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     "(\r)(\t)+(\r)", "\r\r",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            // Remove multiple tabs following a line break with just one tab
            result = System.Text.RegularExpressions.Regex.Replace(result,
                     "(\r)(\t)+", "\r\t",
                     System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            // Initial replacement target string for line breaks
            string breaks = "\r\r\r";
            // Initial replacement target string for tabs
            string tabs = "\t\t\t\t\t";
            for (int index = 0; index < result.Length; index++)
            {
                result = result.Replace(breaks, "\r\r");
                result = result.Replace(tabs, "\t\t\t\t");
                breaks = breaks + "\r";
                tabs = tabs + "\t";
            }

            // That's it.
            return result;
        }
        catch
        {
            return source;
        }
    }

    public static DataSet SQLExec(string sql)
    {
        return DatabaseManager.ExecSQL(sql);
    }

    public static decimal GetDecimalValue(string amount)
    {
        try
        {
            return decimal.Parse(amount.Trim());
        }
        catch (Exception ex)
        {
            return 0;
        }
    }

    public static ACC_ChartOfAccountLabel4 CreateChartOfAccountL4ForMember(string Mem_MemberID,int loginID)
    {
        string sql = @"
Declare @ACC_ChartOfAccountLabel4ID int
Set @ACC_ChartOfAccountLabel4ID= (Select ACC_ChartOfAccountLabel4ID from ACC_ChartOfAccountLabel4 where ACC_HeadTypeID=16 and ExtraField1='" + Mem_MemberID + @"')

if @ACC_ChartOfAccountLabel4ID is NULL
BEGIN
Declare @Name nvarchar(256)
set @Name=(Select Name from [IEB].[dbo].Mem_Member where Mem_MemberID=" + Mem_MemberID + @")

Declare @MembershipNo nvarchar(256)
set @MembershipNo=(Select MembershipNo from [IEB].[dbo].Mem_Member where Mem_MemberID=" + Mem_MemberID + @")

Set @Name+= '('+@MembershipNo+')'

INSERT INTO [ACC_ChartOfAccountLabel4]
           ([Code]
           ,[ACC_HeadTypeID]
           ,[ChartOfAccountLabel4Text]
           ,[ExtraField1]
           ,[ExtraField2]
           ,[ExtraField3]
           ,[AddedBy]
           ,[AddedDate]
           ,[UpdatedBy]
           ,[UpdatedDate]
           ,[RowStatusID])
     VALUES
           (''--<Code, nvarchar(50),>
           ,16--<ACC_HeadTypeID, int,>
           ,@Name--<ChartOfAccountLabel4Text, nvarchar(256),>
           ," + Mem_MemberID + @"--<ExtraField1, nvarchar(256),>
           ,''--<ExtraField2, nvarchar(256),>
           ,''--<ExtraField3, nvarchar(256),>
           ," + loginID + @"--<AddedBy, int,>
           ,GETDATE()--<AddedDate, datetime,>
           ," + loginID + @"--<UpdatedBy, int,>
           ,GETDATE()--<UpdatedDate, datetime,>
           ,1--<RowStatusID, int,>
);

Set @ACC_ChartOfAccountLabel4ID = (Select top 1 ACC_ChartOfAccountLabel4ID from ACC_ChartOfAccountLabel4 order by ACC_ChartOfAccountLabel4ID desc)
END

Select @ACC_ChartOfAccountLabel4ID
";

        DataSet ds = DatabaseManager.ExecSQL(sql);
        return ACC_ChartOfAccountLabel4Manager.GetACC_ChartOfAccountLabel4ByID(int.Parse(ds.Tables[0].Rows[0][0].ToString()));
    }

    public static string NumberToWords(int number)
    {
        if (number == 0)
            return "zero";

        if (number < 0)
            return "minus " + NumberToWords(Math.Abs(number));

        string words = "";

        if ((number / 1000000) > 0)
        {
            words += NumberToWords(number / 1000000) + " Million ";
            number %= 1000000;
        }

        if ((number / 1000) > 0)
        {
            words += NumberToWords(number / 1000) + " Thousand ";
            number %= 1000;
        }

        if ((number / 100) > 0)
        {
            words += NumberToWords(number / 100) + " Hundred ";
            number %= 100;
        }

        if (number > 0)
        {
            if (words != "")
                words += "and ";

            var unitsMap = new[] { "Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen" };
            var tensMap = new[] { "Zero", "Ten", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety" };

            if (number < 20)
                words += unitsMap[number];
            else
            {
                words += tensMap[number / 10];
                if ((number % 10) > 0)
                    words += "-" + unitsMap[number % 10];
            }
        }

        return words;
    }
}